package batch.promionet;

import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;

public class promionetRusTargetGroup
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
    final String FILEPATH = "/home01/sm3/batch/promionet/data/";    

    private OutputStreamWriter oswForData;
    private int                j = 0;

    String dataFileNm = "";

    public promionetRusTargetGroup() {
    }

    public static void main(String [] args) {
        Logger.getLogger("promionet.makeuserno").info("( * ) Start.");

        promionetRusTargetGroup ptUserNo = null;

        ptUserNo = new promionetRusTargetGroup();

        ptUserNo.startMake();

        Logger.getLogger("promionet.makeuserno").info("( * ) End.");
    }

    //Make a log file and a data file.
    private void startMake(){
        dataFileNm = "UAN_TARGETGROUP" + TODAY + ".txt";

        try {
            //To make Data File by 'UTF-8'
            oswForData = new OutputStreamWriter(new FileOutputStream(FILEPATH + dataFileNm, false), "UTF-8");

            getUsers();

        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("promionet.makeuserno").info(ex.toString());
        }
        finally {
            if( oswForData != null ) try { oswForData.close(); } catch(Exception ex) { ex.printStackTrace(); }
        }
    }

    //Obtain Users from DB.
    private void getUsers() throws Exception{
        Connection         conn  = null;
        PreparedStatement  pstmt = null;
        ResultSet          rs    = null;

        String  strID           = "";
        String  strFullSt       = "";

        StringBuffer sbQuery = new StringBuffer();
        sbQuery.append("select a.user_no \n");
        sbQuery.append("from  \n");
        sbQuery.append("    ( \n");
        sbQuery.append("    SELECT  USER_NO                                                   \n");
        sbQuery.append("      FROM  G_MB_MEMBER                                               \n");
        sbQuery.append("     WHERE (LTRIM(RTRIM(EMAIL)) IS NOT NULL AND EMAIL <> 'WAP_TEMP')  \n");
        sbQuery.append("       AND  DELETED IS NULL                                      \n");
        sbQuery.append("  	   AND created > add_months(sysdate, -6)   \n");
        sbQuery.append("    ) a, \n");
        sbQuery.append("    ( \n");
        sbQuery.append("    select user_no \n");
        sbQuery.append("    from g_mb_optin \n");
        sbQuery.append("    where code = '0113001' \n");
        sbQuery.append("    ) b \n");
        sbQuery.append("where a.user_no = b.user_no \n");
        
       
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.14:1521:ORARU","GSMS","imsi00");

			System.out.println("Query: " + sbQuery);

            pstmt = conn.prepareStatement(sbQuery.toString());
            rs = pstmt.executeQuery();

            while(rs.next()){
                strID = ObjectUtils.toString(rs.getObject(1), "");

                //Make Sentences
                strFullSt = strID + "\n";

                //Write down sentences on the file
                oswForData.write(strFullSt);

                j++;
            }

            Logger.getLogger("promionet.makeuserno").info("Total Count : [" + dataFileNm + "] - " + j);
        }
        finally {
            if(rs != null)    try { rs.close(); }    catch (Exception ex) { ex.printStackTrace(); }
            if(pstmt != null) try { pstmt.close(); } catch (Exception ex) { ex.printStackTrace(); }
            if(conn != null)  try { conn.close(); }  catch (Exception ex) { ex.printStackTrace(); }
        }
    }
}    
